{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "dc9692dc-53fb-47a7-9970-b2246633cf60",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d741c2e5-6679-444e-b513-5074bc0ea041",
   "metadata": {},
   "source": [
    "## 读取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "a76fa390-9e0f-492f-a273-c7949ddbdc84",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ts_code</th>\n",
       "      <th>trade_date</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>pre_close</th>\n",
       "      <th>change</th>\n",
       "      <th>pct_chg</th>\n",
       "      <th>vol</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>600519.SH</td>\n",
       "      <td>20241211</td>\n",
       "      <td>1540.00</td>\n",
       "      <td>1555.00</td>\n",
       "      <td>1530.98</td>\n",
       "      <td>1535.60</td>\n",
       "      <td>1546.59</td>\n",
       "      <td>-10.99</td>\n",
       "      <td>-0.7106</td>\n",
       "      <td>29671.12</td>\n",
       "      <td>4569662.604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>600519.SH</td>\n",
       "      <td>20241210</td>\n",
       "      <td>1570.00</td>\n",
       "      <td>1579.73</td>\n",
       "      <td>1545.18</td>\n",
       "      <td>1546.59</td>\n",
       "      <td>1518.80</td>\n",
       "      <td>27.79</td>\n",
       "      <td>1.8297</td>\n",
       "      <td>60312.10</td>\n",
       "      <td>9421340.719</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>600519.SH</td>\n",
       "      <td>20241209</td>\n",
       "      <td>1522.02</td>\n",
       "      <td>1529.72</td>\n",
       "      <td>1513.20</td>\n",
       "      <td>1518.80</td>\n",
       "      <td>1521.01</td>\n",
       "      <td>-2.21</td>\n",
       "      <td>-0.1453</td>\n",
       "      <td>19799.86</td>\n",
       "      <td>3008173.586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>600519.SH</td>\n",
       "      <td>20241206</td>\n",
       "      <td>1513.00</td>\n",
       "      <td>1538.90</td>\n",
       "      <td>1508.07</td>\n",
       "      <td>1521.01</td>\n",
       "      <td>1511.00</td>\n",
       "      <td>10.01</td>\n",
       "      <td>0.6625</td>\n",
       "      <td>32039.69</td>\n",
       "      <td>4883148.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>600519.SH</td>\n",
       "      <td>20241205</td>\n",
       "      <td>1510.86</td>\n",
       "      <td>1517.86</td>\n",
       "      <td>1510.00</td>\n",
       "      <td>1511.00</td>\n",
       "      <td>1520.00</td>\n",
       "      <td>-9.00</td>\n",
       "      <td>-0.5921</td>\n",
       "      <td>16141.47</td>\n",
       "      <td>2441318.387</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ts_code  trade_date     open     high      low    close  pre_close  \\\n",
       "0  600519.SH    20241211  1540.00  1555.00  1530.98  1535.60    1546.59   \n",
       "1  600519.SH    20241210  1570.00  1579.73  1545.18  1546.59    1518.80   \n",
       "2  600519.SH    20241209  1522.02  1529.72  1513.20  1518.80    1521.01   \n",
       "3  600519.SH    20241206  1513.00  1538.90  1508.07  1521.01    1511.00   \n",
       "4  600519.SH    20241205  1510.86  1517.86  1510.00  1511.00    1520.00   \n",
       "\n",
       "   change  pct_chg       vol       amount  \n",
       "0  -10.99  -0.7106  29671.12  4569662.604  \n",
       "1   27.79   1.8297  60312.10  9421340.719  \n",
       "2   -2.21  -0.1453  19799.86  3008173.586  \n",
       "3   10.01   0.6625  32039.69  4883148.600  \n",
       "4   -9.00  -0.5921  16141.47  2441318.387  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 成分股数据\n",
    "df=pd.read_excel(\"df_all.xlsx\",index_col=0)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b84a2127-7a56-4452-a5bd-e58afa8f88be",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>成份券代码Constituent Code</th>\n",
       "      <th>成份券名称Constituent Name</th>\n",
       "      <th>交易所Exchange</th>\n",
       "      <th>权重(%)weight</th>\n",
       "      <th>地区</th>\n",
       "      <th>ts_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>600519</td>\n",
       "      <td>贵州茅台</td>\n",
       "      <td>上海证券交易所</td>\n",
       "      <td>4.767</td>\n",
       "      <td>SH</td>\n",
       "      <td>600519.SH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>300750</td>\n",
       "      <td>宁德时代</td>\n",
       "      <td>深圳证券交易所</td>\n",
       "      <td>3.430</td>\n",
       "      <td>SZ</td>\n",
       "      <td>300750.SZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>601318</td>\n",
       "      <td>中国平安</td>\n",
       "      <td>上海证券交易所</td>\n",
       "      <td>2.851</td>\n",
       "      <td>SH</td>\n",
       "      <td>601318.SH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>600036</td>\n",
       "      <td>招商银行</td>\n",
       "      <td>上海证券交易所</td>\n",
       "      <td>2.238</td>\n",
       "      <td>SH</td>\n",
       "      <td>600036.SH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>300059</td>\n",
       "      <td>东方财富</td>\n",
       "      <td>深圳证券交易所</td>\n",
       "      <td>1.713</td>\n",
       "      <td>SZ</td>\n",
       "      <td>300059.SZ</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   成份券代码Constituent Code 成份券名称Constituent Name 交易所Exchange  权重(%)weight  地区  \\\n",
       "0                 600519                  贵州茅台     上海证券交易所        4.767  SH   \n",
       "1                 300750                  宁德时代     深圳证券交易所        3.430  SZ   \n",
       "2                 601318                  中国平安     上海证券交易所        2.851  SH   \n",
       "3                 600036                  招商银行     上海证券交易所        2.238  SH   \n",
       "4                 300059                  东方财富     深圳证券交易所        1.713  SZ   \n",
       "\n",
       "     ts_code  \n",
       "0  600519.SH  \n",
       "1  300750.SZ  \n",
       "2  601318.SH  \n",
       "3  600036.SH  \n",
       "4  300059.SZ  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 沪深300指数数据\n",
    "df300=pd.read_excel(\"df_300_w.xlsx\",index_col=0)\n",
    "df300.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "acbb814f-de34-4193-af61-4753ae9b5dba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>成份券代码Constituent Code</th>\n",
       "      <th>成份券名称Constituent Name</th>\n",
       "      <th>交易所Exchange</th>\n",
       "      <th>权重(%)weight</th>\n",
       "      <th>地区</th>\n",
       "      <th>ts_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>600519</td>\n",
       "      <td>贵州茅台</td>\n",
       "      <td>上海证券交易所</td>\n",
       "      <td>4.767</td>\n",
       "      <td>SH</td>\n",
       "      <td>600519.SH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>300750</td>\n",
       "      <td>宁德时代</td>\n",
       "      <td>深圳证券交易所</td>\n",
       "      <td>3.430</td>\n",
       "      <td>SZ</td>\n",
       "      <td>300750.SZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>601318</td>\n",
       "      <td>中国平安</td>\n",
       "      <td>上海证券交易所</td>\n",
       "      <td>2.851</td>\n",
       "      <td>SH</td>\n",
       "      <td>601318.SH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>600036</td>\n",
       "      <td>招商银行</td>\n",
       "      <td>上海证券交易所</td>\n",
       "      <td>2.238</td>\n",
       "      <td>SH</td>\n",
       "      <td>600036.SH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>300059</td>\n",
       "      <td>东方财富</td>\n",
       "      <td>深圳证券交易所</td>\n",
       "      <td>1.713</td>\n",
       "      <td>SZ</td>\n",
       "      <td>300059.SZ</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   成份券代码Constituent Code 成份券名称Constituent Name 交易所Exchange  权重(%)weight  地区  \\\n",
       "0                 600519                  贵州茅台     上海证券交易所        4.767  SH   \n",
       "1                 300750                  宁德时代     深圳证券交易所        3.430  SZ   \n",
       "2                 601318                  中国平安     上海证券交易所        2.851  SH   \n",
       "3                 600036                  招商银行     上海证券交易所        2.238  SH   \n",
       "4                 300059                  东方财富     深圳证券交易所        1.713  SZ   \n",
       "\n",
       "     ts_code  \n",
       "0  600519.SH  \n",
       "1  300750.SZ  \n",
       "2  601318.SH  \n",
       "3  600036.SH  \n",
       "4  300059.SZ  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 根据权重从大到小选择前20支股票\n",
    "df20=df300.loc[:19,:]\n",
    "df20.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a645200d-fb9a-4dd6-8f7d-aaf263e6813e",
   "metadata": {},
   "source": [
    "## 变成时间序列数据\n",
    "每一列为一个股票在3年的涨跌幅数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "aa05c4a9-4aec-48b7-a7d9-e8c98399831d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>pct_chg</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trade_date</th>\n",
       "      <th>ts_code</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">20211210</th>\n",
       "      <th>300014.SZ</th>\n",
       "      <td>1.2918</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300015.SZ</th>\n",
       "      <td>-2.9965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300033.SZ</th>\n",
       "      <td>-3.1319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300059.SZ</th>\n",
       "      <td>-0.8864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300122.SZ</th>\n",
       "      <td>-2.1301</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      pct_chg\n",
       "trade_date ts_code           \n",
       "20211210   300014.SZ   1.2918\n",
       "           300015.SZ  -2.9965\n",
       "           300033.SZ  -3.1319\n",
       "           300059.SZ  -0.8864\n",
       "           300122.SZ  -2.1301"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1=pd.pivot_table(df,index=[\"trade_date\",\"ts_code\"],values=[\"pct_chg\"])\n",
    "df1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3264f654-f2d1-4d86-93a5-11bce2e993e5",
   "metadata": {},
   "source": [
    "找一天算一下涨跌幅"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "11faa48a-7705-44e0-bb2e-3473c54e47e2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trade_date</th>\n",
       "      <th>ts_code</th>\n",
       "      <th>pct_chg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20211210</td>\n",
       "      <td>300014.SZ</td>\n",
       "      <td>1.2918</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20211210</td>\n",
       "      <td>300015.SZ</td>\n",
       "      <td>-2.9965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20211210</td>\n",
       "      <td>300033.SZ</td>\n",
       "      <td>-3.1319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20211210</td>\n",
       "      <td>300059.SZ</td>\n",
       "      <td>-0.8864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20211210</td>\n",
       "      <td>300122.SZ</td>\n",
       "      <td>-2.1301</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   trade_date    ts_code  pct_chg\n",
       "0    20211210  300014.SZ   1.2918\n",
       "1    20211210  300015.SZ  -2.9965\n",
       "2    20211210  300033.SZ  -3.1319\n",
       "3    20211210  300059.SZ  -0.8864\n",
       "4    20211210  300122.SZ  -2.1301"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 300支股票20241211涨跌幅\n",
    "df_i=df1.query('trade_date == [20211210]')\n",
    "df_i=df_i.reset_index()\n",
    "df_i.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "113516c1-af8a-490f-931d-038cda9a674c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trade_date</th>\n",
       "      <th>ts_code</th>\n",
       "      <th>pct_chg</th>\n",
       "      <th>权重(%)weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20211210</td>\n",
       "      <td>300014.SZ</td>\n",
       "      <td>1.2918</td>\n",
       "      <td>0.297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20211210</td>\n",
       "      <td>300015.SZ</td>\n",
       "      <td>-2.9965</td>\n",
       "      <td>0.341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20211210</td>\n",
       "      <td>300033.SZ</td>\n",
       "      <td>-3.1319</td>\n",
       "      <td>0.359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20211210</td>\n",
       "      <td>300059.SZ</td>\n",
       "      <td>-0.8864</td>\n",
       "      <td>1.713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20211210</td>\n",
       "      <td>300122.SZ</td>\n",
       "      <td>-2.1301</td>\n",
       "      <td>0.175</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   trade_date    ts_code  pct_chg  权重(%)weight\n",
       "0    20211210  300014.SZ   1.2918        0.297\n",
       "1    20211210  300015.SZ  -2.9965        0.341\n",
       "2    20211210  300033.SZ  -3.1319        0.359\n",
       "3    20211210  300059.SZ  -0.8864        1.713\n",
       "4    20211210  300122.SZ  -2.1301        0.175"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 连表\n",
    "df_i_w=pd.merge(df_i,df300[['权重(%)weight','ts_code']],how='inner',on='ts_code')\n",
    "df_i_w.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2c7d8273-fbac-45b3-bf9e-bb788d5f3305",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # len(df_i_w)\n",
    "# len(df_i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a157413e-2b02-4f78-b41d-e276d9877dc5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-0.15933332800000002"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 计算\n",
    "chg=np.sum(np.array(df_i_w['pct_chg'].tolist())/100*np.array(df_i_w['权重(%)weight'].tolist())/100)*100\n",
    "chg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a6def9e1-ce81-4cc5-bd34-cb4642cafbb5",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "H:\\anaconda3\\Lib\\site-packages\\openpyxl\\styles\\stylesheet.py:226: UserWarning: Workbook contains no default style, apply openpyxl's default\n",
      "  warn(\"Workbook contains no default style, apply openpyxl's default\")\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期Date</th>\n",
       "      <th>指数代码Index Code</th>\n",
       "      <th>指数中文全称Index Chinese Name(Full)</th>\n",
       "      <th>指数中文简称Index Chinese Name</th>\n",
       "      <th>指数英文全称Index English Name(Full)</th>\n",
       "      <th>指数英文简称Index Chinese Name</th>\n",
       "      <th>开盘Open</th>\n",
       "      <th>最高High</th>\n",
       "      <th>最低Low</th>\n",
       "      <th>收盘Close</th>\n",
       "      <th>涨跌Change</th>\n",
       "      <th>涨跌幅(%)Change(%)</th>\n",
       "      <th>成交量（万手）Volume(M Shares)</th>\n",
       "      <th>成交金额（亿元）Turnover</th>\n",
       "      <th>样本数量ConsNumber</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20211210</td>\n",
       "      <td>300</td>\n",
       "      <td>沪深300指数</td>\n",
       "      <td>沪深300</td>\n",
       "      <td>CSI 300 Index</td>\n",
       "      <td>CSI 300</td>\n",
       "      <td>5046.27</td>\n",
       "      <td>5060.06</td>\n",
       "      <td>5035.81</td>\n",
       "      <td>5055.12</td>\n",
       "      <td>-23.57</td>\n",
       "      <td>-0.46</td>\n",
       "      <td>19369.52</td>\n",
       "      <td>3569.18</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20211213</td>\n",
       "      <td>300</td>\n",
       "      <td>沪深300指数</td>\n",
       "      <td>沪深300</td>\n",
       "      <td>CSI 300 Index</td>\n",
       "      <td>CSI 300</td>\n",
       "      <td>5090.02</td>\n",
       "      <td>5143.84</td>\n",
       "      <td>5079.73</td>\n",
       "      <td>5083.80</td>\n",
       "      <td>28.68</td>\n",
       "      <td>0.57</td>\n",
       "      <td>21180.16</td>\n",
       "      <td>4263.77</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20211214</td>\n",
       "      <td>300</td>\n",
       "      <td>沪深300指数</td>\n",
       "      <td>沪深300</td>\n",
       "      <td>CSI 300 Index</td>\n",
       "      <td>CSI 300</td>\n",
       "      <td>5066.35</td>\n",
       "      <td>5075.21</td>\n",
       "      <td>5038.86</td>\n",
       "      <td>5049.70</td>\n",
       "      <td>-34.11</td>\n",
       "      <td>-0.67</td>\n",
       "      <td>15910.78</td>\n",
       "      <td>3186.86</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20211215</td>\n",
       "      <td>300</td>\n",
       "      <td>沪深300指数</td>\n",
       "      <td>沪深300</td>\n",
       "      <td>CSI 300 Index</td>\n",
       "      <td>CSI 300</td>\n",
       "      <td>5036.28</td>\n",
       "      <td>5060.37</td>\n",
       "      <td>5003.78</td>\n",
       "      <td>5005.90</td>\n",
       "      <td>-43.80</td>\n",
       "      <td>-0.87</td>\n",
       "      <td>15052.98</td>\n",
       "      <td>2910.73</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20211216</td>\n",
       "      <td>300</td>\n",
       "      <td>沪深300指数</td>\n",
       "      <td>沪深300</td>\n",
       "      <td>CSI 300 Index</td>\n",
       "      <td>CSI 300</td>\n",
       "      <td>5006.61</td>\n",
       "      <td>5034.73</td>\n",
       "      <td>4987.22</td>\n",
       "      <td>5034.73</td>\n",
       "      <td>28.83</td>\n",
       "      <td>0.58</td>\n",
       "      <td>14405.39</td>\n",
       "      <td>2914.75</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     日期Date  指数代码Index Code 指数中文全称Index Chinese Name(Full)  \\\n",
       "0  20211210             300                        沪深300指数   \n",
       "1  20211213             300                        沪深300指数   \n",
       "2  20211214             300                        沪深300指数   \n",
       "3  20211215             300                        沪深300指数   \n",
       "4  20211216             300                        沪深300指数   \n",
       "\n",
       "  指数中文简称Index Chinese Name 指数英文全称Index English Name(Full)  \\\n",
       "0                    沪深300                  CSI 300 Index   \n",
       "1                    沪深300                  CSI 300 Index   \n",
       "2                    沪深300                  CSI 300 Index   \n",
       "3                    沪深300                  CSI 300 Index   \n",
       "4                    沪深300                  CSI 300 Index   \n",
       "\n",
       "  指数英文简称Index Chinese Name   开盘Open   最高High    最低Low  收盘Close  涨跌Change  \\\n",
       "0                  CSI 300  5046.27  5060.06  5035.81  5055.12    -23.57   \n",
       "1                  CSI 300  5090.02  5143.84  5079.73  5083.80     28.68   \n",
       "2                  CSI 300  5066.35  5075.21  5038.86  5049.70    -34.11   \n",
       "3                  CSI 300  5036.28  5060.37  5003.78  5005.90    -43.80   \n",
       "4                  CSI 300  5006.61  5034.73  4987.22  5034.73     28.83   \n",
       "\n",
       "   涨跌幅(%)Change(%)  成交量（万手）Volume(M Shares)  成交金额（亿元）Turnover  样本数量ConsNumber  \n",
       "0            -0.46                 19369.52           3569.18             300  \n",
       "1             0.57                 21180.16           4263.77             300  \n",
       "2            -0.67                 15910.78           3186.86             300  \n",
       "3            -0.87                 15052.98           2910.73             300  \n",
       "4             0.58                 14405.39           2914.75             300  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df300_daily=pd.read_excel('沪深300指数近3年.xlsx')\n",
    "df300_daily.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "3ed8199c-ab4a-4178-872e-9874f346abbc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "727   -0.17\n",
       "Name: 涨跌幅(%)Change(%), dtype: float64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df300_daily[df300_daily['日期Date']==20241211]['涨跌幅(%)Change(%)']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9ca502f-ac69-43e1-b6bc-a3b9fe773aff",
   "metadata": {},
   "source": [
    "## 找到指数权重前20支股票的近3年每天的涨跌幅时序数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "6966b109-b6f5-4d94-9ed8-e5074ec002e9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ts_code</th>\n",
       "      <th>trade_date</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>pre_close</th>\n",
       "      <th>change</th>\n",
       "      <th>pct_chg</th>\n",
       "      <th>vol</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>600519.SH</td>\n",
       "      <td>20241211</td>\n",
       "      <td>1540.00</td>\n",
       "      <td>1555.00</td>\n",
       "      <td>1530.98</td>\n",
       "      <td>1535.60</td>\n",
       "      <td>1546.59</td>\n",
       "      <td>-10.99</td>\n",
       "      <td>-0.7106</td>\n",
       "      <td>29671.12</td>\n",
       "      <td>4569662.604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>600519.SH</td>\n",
       "      <td>20241210</td>\n",
       "      <td>1570.00</td>\n",
       "      <td>1579.73</td>\n",
       "      <td>1545.18</td>\n",
       "      <td>1546.59</td>\n",
       "      <td>1518.80</td>\n",
       "      <td>27.79</td>\n",
       "      <td>1.8297</td>\n",
       "      <td>60312.10</td>\n",
       "      <td>9421340.719</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>600519.SH</td>\n",
       "      <td>20241209</td>\n",
       "      <td>1522.02</td>\n",
       "      <td>1529.72</td>\n",
       "      <td>1513.20</td>\n",
       "      <td>1518.80</td>\n",
       "      <td>1521.01</td>\n",
       "      <td>-2.21</td>\n",
       "      <td>-0.1453</td>\n",
       "      <td>19799.86</td>\n",
       "      <td>3008173.586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>600519.SH</td>\n",
       "      <td>20241206</td>\n",
       "      <td>1513.00</td>\n",
       "      <td>1538.90</td>\n",
       "      <td>1508.07</td>\n",
       "      <td>1521.01</td>\n",
       "      <td>1511.00</td>\n",
       "      <td>10.01</td>\n",
       "      <td>0.6625</td>\n",
       "      <td>32039.69</td>\n",
       "      <td>4883148.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>600519.SH</td>\n",
       "      <td>20241205</td>\n",
       "      <td>1510.86</td>\n",
       "      <td>1517.86</td>\n",
       "      <td>1510.00</td>\n",
       "      <td>1511.00</td>\n",
       "      <td>1520.00</td>\n",
       "      <td>-9.00</td>\n",
       "      <td>-0.5921</td>\n",
       "      <td>16141.47</td>\n",
       "      <td>2441318.387</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ts_code  trade_date     open     high      low    close  pre_close  \\\n",
       "0  600519.SH    20241211  1540.00  1555.00  1530.98  1535.60    1546.59   \n",
       "1  600519.SH    20241210  1570.00  1579.73  1545.18  1546.59    1518.80   \n",
       "2  600519.SH    20241209  1522.02  1529.72  1513.20  1518.80    1521.01   \n",
       "3  600519.SH    20241206  1513.00  1538.90  1508.07  1521.01    1511.00   \n",
       "4  600519.SH    20241205  1510.86  1517.86  1510.00  1511.00    1520.00   \n",
       "\n",
       "   change  pct_chg       vol       amount  \n",
       "0  -10.99  -0.7106  29671.12  4569662.604  \n",
       "1   27.79   1.8297  60312.10  9421340.719  \n",
       "2   -2.21  -0.1453  19799.86  3008173.586  \n",
       "3   10.01   0.6625  32039.69  4883148.600  \n",
       "4   -9.00  -0.5921  16141.47  2441318.387  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts_code_20=df20['ts_code'].tolist()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "367806a3-9809-4f4b-bfa8-2b89fa096020",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "dff=pd.DataFrame(columns=list(df.columns))\n",
    "for i in df.index:\n",
    "    if df.loc[i,'ts_code'] in ts_code_20:\n",
    "        dff=pd.concat([dff,df.loc[i,:]])\n",
    "dff.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "b5322c98-9682-4070-b2d7-4964d8f7943e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "\"None of [Index(['600519.SH', '300750.SZ', '601318.SH', '600036.SH', '300059.SZ',\\n       '333.SZ', '600900.SH', '600030.SH', '858.SZ', '601166.SH', '601899.SH',\\n       '2594.SZ', '600276.SH', '601398.SH', '601328.SH', '2475.SZ', '651.SZ',\\n       '600887.SH', '688981.SH', '725.SZ'],\\n      dtype='object', name='trade_date')] are in the [index]\"",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[20], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m dff20\u001b[38;5;241m=\u001b[39mdff\u001b[38;5;241m.\u001b[39mloc[ts_code_20,:]\n\u001b[0;32m      2\u001b[0m dff20\u001b[38;5;241m.\u001b[39mhead()\n",
      "File \u001b[1;32mH:\\anaconda3\\Lib\\site-packages\\pandas\\core\\indexing.py:1184\u001b[0m, in \u001b[0;36m_LocationIndexer.__getitem__\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m   1182\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_is_scalar_access(key):\n\u001b[0;32m   1183\u001b[0m         \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mobj\u001b[38;5;241m.\u001b[39m_get_value(\u001b[38;5;241m*\u001b[39mkey, takeable\u001b[38;5;241m=\u001b[39m\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_takeable)\n\u001b[1;32m-> 1184\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_getitem_tuple(key)\n\u001b[0;32m   1185\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m   1186\u001b[0m     \u001b[38;5;66;03m# we by definition only have the 0th axis\u001b[39;00m\n\u001b[0;32m   1187\u001b[0m     axis \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39maxis \u001b[38;5;129;01mor\u001b[39;00m \u001b[38;5;241m0\u001b[39m\n",
      "File \u001b[1;32mH:\\anaconda3\\Lib\\site-packages\\pandas\\core\\indexing.py:1377\u001b[0m, in \u001b[0;36m_LocIndexer._getitem_tuple\u001b[1;34m(self, tup)\u001b[0m\n\u001b[0;32m   1374\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_multi_take_opportunity(tup):\n\u001b[0;32m   1375\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_multi_take(tup)\n\u001b[1;32m-> 1377\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_getitem_tuple_same_dim(tup)\n",
      "File \u001b[1;32mH:\\anaconda3\\Lib\\site-packages\\pandas\\core\\indexing.py:1020\u001b[0m, in \u001b[0;36m_LocationIndexer._getitem_tuple_same_dim\u001b[1;34m(self, tup)\u001b[0m\n\u001b[0;32m   1017\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m com\u001b[38;5;241m.\u001b[39mis_null_slice(key):\n\u001b[0;32m   1018\u001b[0m     \u001b[38;5;28;01mcontinue\u001b[39;00m\n\u001b[1;32m-> 1020\u001b[0m retval \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mgetattr\u001b[39m(retval, \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mname)\u001b[38;5;241m.\u001b[39m_getitem_axis(key, axis\u001b[38;5;241m=\u001b[39mi)\n\u001b[0;32m   1021\u001b[0m \u001b[38;5;66;03m# We should never have retval.ndim < self.ndim, as that should\u001b[39;00m\n\u001b[0;32m   1022\u001b[0m \u001b[38;5;66;03m#  be handled by the _getitem_lowerdim call above.\u001b[39;00m\n\u001b[0;32m   1023\u001b[0m \u001b[38;5;28;01massert\u001b[39;00m retval\u001b[38;5;241m.\u001b[39mndim \u001b[38;5;241m==\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mndim\n",
      "File \u001b[1;32mH:\\anaconda3\\Lib\\site-packages\\pandas\\core\\indexing.py:1420\u001b[0m, in \u001b[0;36m_LocIndexer._getitem_axis\u001b[1;34m(self, key, axis)\u001b[0m\n\u001b[0;32m   1417\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mhasattr\u001b[39m(key, \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mndim\u001b[39m\u001b[38;5;124m\"\u001b[39m) \u001b[38;5;129;01mand\u001b[39;00m key\u001b[38;5;241m.\u001b[39mndim \u001b[38;5;241m>\u001b[39m \u001b[38;5;241m1\u001b[39m:\n\u001b[0;32m   1418\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mValueError\u001b[39;00m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mCannot index with multidimensional key\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[1;32m-> 1420\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_getitem_iterable(key, axis\u001b[38;5;241m=\u001b[39maxis)\n\u001b[0;32m   1422\u001b[0m \u001b[38;5;66;03m# nested tuple slicing\u001b[39;00m\n\u001b[0;32m   1423\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m is_nested_tuple(key, labels):\n",
      "File \u001b[1;32mH:\\anaconda3\\Lib\\site-packages\\pandas\\core\\indexing.py:1360\u001b[0m, in \u001b[0;36m_LocIndexer._getitem_iterable\u001b[1;34m(self, key, axis)\u001b[0m\n\u001b[0;32m   1357\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_validate_key(key, axis)\n\u001b[0;32m   1359\u001b[0m \u001b[38;5;66;03m# A collection of keys\u001b[39;00m\n\u001b[1;32m-> 1360\u001b[0m keyarr, indexer \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_get_listlike_indexer(key, axis)\n\u001b[0;32m   1361\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mobj\u001b[38;5;241m.\u001b[39m_reindex_with_indexers(\n\u001b[0;32m   1362\u001b[0m     {axis: [keyarr, indexer]}, copy\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mTrue\u001b[39;00m, allow_dups\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mTrue\u001b[39;00m\n\u001b[0;32m   1363\u001b[0m )\n",
      "File \u001b[1;32mH:\\anaconda3\\Lib\\site-packages\\pandas\\core\\indexing.py:1558\u001b[0m, in \u001b[0;36m_LocIndexer._get_listlike_indexer\u001b[1;34m(self, key, axis)\u001b[0m\n\u001b[0;32m   1555\u001b[0m ax \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mobj\u001b[38;5;241m.\u001b[39m_get_axis(axis)\n\u001b[0;32m   1556\u001b[0m axis_name \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mobj\u001b[38;5;241m.\u001b[39m_get_axis_name(axis)\n\u001b[1;32m-> 1558\u001b[0m keyarr, indexer \u001b[38;5;241m=\u001b[39m ax\u001b[38;5;241m.\u001b[39m_get_indexer_strict(key, axis_name)\n\u001b[0;32m   1560\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m keyarr, indexer\n",
      "File \u001b[1;32mH:\\anaconda3\\Lib\\site-packages\\pandas\\core\\indexes\\base.py:6200\u001b[0m, in \u001b[0;36mIndex._get_indexer_strict\u001b[1;34m(self, key, axis_name)\u001b[0m\n\u001b[0;32m   6197\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m   6198\u001b[0m     keyarr, indexer, new_indexer \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_reindex_non_unique(keyarr)\n\u001b[1;32m-> 6200\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_raise_if_missing(keyarr, indexer, axis_name)\n\u001b[0;32m   6202\u001b[0m keyarr \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mtake(indexer)\n\u001b[0;32m   6203\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(key, Index):\n\u001b[0;32m   6204\u001b[0m     \u001b[38;5;66;03m# GH 42790 - Preserve name from an Index\u001b[39;00m\n",
      "File \u001b[1;32mH:\\anaconda3\\Lib\\site-packages\\pandas\\core\\indexes\\base.py:6249\u001b[0m, in \u001b[0;36mIndex._raise_if_missing\u001b[1;34m(self, key, indexer, axis_name)\u001b[0m\n\u001b[0;32m   6247\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m nmissing:\n\u001b[0;32m   6248\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m nmissing \u001b[38;5;241m==\u001b[39m \u001b[38;5;28mlen\u001b[39m(indexer):\n\u001b[1;32m-> 6249\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m(\u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mNone of [\u001b[39m\u001b[38;5;132;01m{\u001b[39;00mkey\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m] are in the [\u001b[39m\u001b[38;5;132;01m{\u001b[39;00maxis_name\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m]\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m   6251\u001b[0m     not_found \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mlist\u001b[39m(ensure_index(key)[missing_mask\u001b[38;5;241m.\u001b[39mnonzero()[\u001b[38;5;241m0\u001b[39m]]\u001b[38;5;241m.\u001b[39munique())\n\u001b[0;32m   6252\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m(\u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;132;01m{\u001b[39;00mnot_found\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m not in index\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "\u001b[1;31mKeyError\u001b[0m: \"None of [Index(['600519.SH', '300750.SZ', '601318.SH', '600036.SH', '300059.SZ',\\n       '333.SZ', '600900.SH', '600030.SH', '858.SZ', '601166.SH', '601899.SH',\\n       '2594.SZ', '600276.SH', '601398.SH', '601328.SH', '2475.SZ', '651.SZ',\\n       '600887.SH', '688981.SH', '725.SZ'],\\n      dtype='object', name='trade_date')] are in the [index]\""
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fe5e1709-e2ec-43c3-a666-d9ecbeb69d77",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "cfbf8c70-1fed-4045-81f0-9ac16110eff0",
   "metadata": {},
   "source": [
    "## 开始优化\n",
    "现在有数据：<br />\n",
    "1. 沪深300指数数据：df300\n",
    "2. 沪深300指数近3年每天的数据：df300_daily\n",
    "3. 成分股近3年每天的数据：df\n",
    "4. 成分股近3年涨跌幅时序数据：df1\n",
    "5. 指数权重前20支股票的代码与权重值：df20\n",
    "<br />\n",
    "\n",
    "目标是最小化绝对误差，找到一组不超过20支股票买入的权重w"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "35bba4cd-7c26-4f3c-a603-aee3535524d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import mfo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "f48ccab1-893a-4351-b147-1cb2d7f5b255",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 方法：飞蛾扑火优化算法\n",
    "lb=np.array([0]*20) # 下界\n",
    "ub=np.array([1]*20) # 上界\n",
    "max_i=100\n",
    "dim=20\n",
    "pop=100\n",
    "chgi=df1\n",
    "chg300=df300_daily[['日期Date','涨跌幅(%)Change(%)']]\n",
    "p=10 # 惩罚因子\n",
    "index2codes=ts_code_20"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "36d086cc-8214-4aa4-8001-89d2d3b81fdf",
   "metadata": {},
   "outputs": [],
   "source": [
    "mymfo=mfo.MFO(pop,max_i,dim,ub,lb,chgi,chg300,index2codes,p)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "087211d2-eb76-4ea5-8111-7ba4eb3d362c",
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'MFO' object has no attribute 'n'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[17], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m mymfo\u001b[38;5;241m.\u001b[39mrun()\n",
      "File \u001b[1;32mG:\\研二\\实习\\招证-量化\\code\\mfo.py:86\u001b[0m, in \u001b[0;36mMFO.run\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m     83\u001b[0m N\u001b[38;5;241m=\u001b[39m\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mN\n\u001b[0;32m     84\u001b[0m iterx, max_iter \u001b[38;5;241m=\u001b[39m \u001b[38;5;241m0\u001b[39m, \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39miter\n\u001b[1;32m---> 86\u001b[0m X0\u001b[38;5;241m=\u001b[39m\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mini_population() \u001b[38;5;66;03m# 初始种群位置\u001b[39;00m\n\u001b[0;32m     87\u001b[0m fit\u001b[38;5;241m=\u001b[39m\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mgetFitness(X0) \u001b[38;5;66;03m# 初始每个个体的适应值\u001b[39;00m\n\u001b[0;32m     89\u001b[0m Moth_pos \u001b[38;5;241m=\u001b[39m X0 \u001b[38;5;66;03m# 记录更新后的飞蛾位置\u001b[39;00m\n",
      "File \u001b[1;32mG:\\研二\\实习\\招证-量化\\code\\mfo.py:30\u001b[0m, in \u001b[0;36mMFO.ini_population\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m     26\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mini_population\u001b[39m(\u001b[38;5;28mself\u001b[39m):\n\u001b[0;32m     27\u001b[0m     \u001b[38;5;66;03m# 初始化种群:平均分配\u001b[39;00m\n\u001b[0;32m     28\u001b[0m     \u001b[38;5;66;03m# 都是np.array类型\u001b[39;00m\n\u001b[0;32m     29\u001b[0m     population, fitness \u001b[38;5;241m=\u001b[39m [], []\n\u001b[1;32m---> 30\u001b[0m     \u001b[38;5;28;01mfor\u001b[39;00m i \u001b[38;5;129;01min\u001b[39;00m \u001b[38;5;28mrange\u001b[39m(\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mn):\n\u001b[0;32m     31\u001b[0m         moth \u001b[38;5;241m=\u001b[39mnp\u001b[38;5;241m.\u001b[39marray([\u001b[38;5;241m1\u001b[39m\u001b[38;5;241m/\u001b[39m\u001b[38;5;241m20\u001b[39m]\u001b[38;5;241m*\u001b[39m\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mdim)\n\u001b[0;32m     32\u001b[0m         population\u001b[38;5;241m.\u001b[39mappend(moth)\n",
      "\u001b[1;31mAttributeError\u001b[0m: 'MFO' object has no attribute 'n'"
     ]
    }
   ],
   "source": [
    "mymfo.run()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fdaecb7c-d381-498a-af16-1d90cf8911ac",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "f39ea113-23eb-451f-9967-be0548f89316",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0.05, 0.05, 0.05, 0.05, 0.05, 0.05, 0.05, 0.05, 0.05, 0.05])"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.array([1/20]*10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ddccd567-d045-439c-ace9-f3692addf467",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9a4525c7-c0bb-4223-9480-db6eebb9ebf6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fb5a04a3-4f76-4c28-b359-84c2b314a8d1",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "494cb313-d095-458f-8e98-53a673eec273",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03ea4f10-ee64-4052-be4f-bbf8d9e55543",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76125cbb-2a08-42f5-a7a5-1ab2e6ff26c3",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
